/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package lk.mrt.msc.swa.epid.db;

/**
 *
 * @author Nadesh
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Vector;

public class MySQLAccess {
    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public MySQLAccess() {
    }

    private void createConnection () throws Exception{
        try {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("com.mysql.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager.getConnection("jdbc:mysql://localhost/Epid?"
                    + "user=root&password=Password");
        }catch (Exception e) {
            throw e;
        } finally {
            close();
        }
    }
    
    public boolean addIncident(Vector vect) throws Exception{
        boolean status = true;
        createConnection ();
        //Insert to Patient
        preparedStatement = connect.prepareStatement("INSERT INTO `epid`.`patient` (`Name`, `DOB`, `Sex`, `Ethnicity`, `Occupation`, `Address`, `District`) VALUES (?,?,?,?,?,?,?)");
            
           // Parameters start with 1
            preparedStatement.setString(1, vect.get(2).toString());
            preparedStatement.setDate(2, new java.sql.Date(Integer.parseInt(vect.get(5).toString()), Integer.parseInt(vect.get(4).toString()),Integer.parseInt(vect.get(3).toString())));
            preparedStatement.setString(3, vect.get(6).toString());
            preparedStatement.setString(4, vect.get(7).toString());
            preparedStatement.setString(5, vect.get(8).toString());
            preparedStatement.setString(6, vect.get(9).toString());
            preparedStatement.setString(7, vect.get(10).toString());
                       
            preparedStatement.executeUpdate();
            
            
             statement = connect.createStatement();
            // Result set get the result of the SQL query
            resultSet = statement.executeQuery("select last_insert_id()");
            int PatientID = 0;
            if (resultSet.next()) {
                PatientID = resultSet.getInt(1);
            }
            close();
            
            //Add Illness
            preparedStatement = connect.prepareStatement("INSERT INTO `epid`.`illness` (`PatientID`, `DateOnset`, `TreatedHospital`, `Admission`, `Wardno`, `BHTno`, `Outcome`, `DateDischarge`) VALUES (?,?,?,?,?,?,?,?)");
            
           // Parameters start with 1
            preparedStatement.setInt(1, PatientID);
            preparedStatement.setDate(2, new java.sql.Date(Integer.parseInt(vect.get(13).toString()), Integer.parseInt(vect.get(12).toString()),Integer.parseInt(vect.get(11).toString())));
            preparedStatement.setString(3, vect.get(14).toString());
            preparedStatement.setDate(4, new java.sql.Date(Integer.parseInt(vect.get(17).toString()), Integer.parseInt(vect.get(16).toString()),Integer.parseInt(vect.get(15).toString())));
            
            preparedStatement.setString(5, vect.get(18).toString());
            preparedStatement.setString(6, vect.get(19).toString());
            preparedStatement.setString(7, vect.get(20).toString());
            preparedStatement.setDate(8, new java.sql.Date(Integer.parseInt(vect.get(23).toString()), Integer.parseInt(vect.get(22).toString()),Integer.parseInt(vect.get(21).toString())));
            
                       
            preparedStatement.executeUpdate();
            
            
             statement = connect.createStatement();
            // Result set get the result of the SQL query
            resultSet = statement.executeQuery("select last_insert_id()");
            int IllnessID = 0;
            if (resultSet.next()) {
                IllnessID = resultSet.getInt(1);
            }
            close();
            
            //Add to incident
            //Add Illness
            preparedStatement = connect.prepareStatement("INSERT INTO `epid`.`incident` ( `Hospital`, `CaseNo`, `PatientID`, `IllnessID`, `Time`) VALUES (?,?,?,?,NOW())");
            
           // Parameters start with 1
            preparedStatement.setString(1, vect.get(0).toString());
            preparedStatement.setString(2, vect.get(1).toString());
            preparedStatement.setInt(3, PatientID);
            preparedStatement.setInt(4, IllnessID);
                       
              
                       
            preparedStatement.executeUpdate();
            
            close();
            if (connect != null) {
               connect.close();
           }
            return status;
    }
    
     
    
    public void readDataBase() throws Exception {
        try {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("com.mysql.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager.getConnection("jdbc:mysql://localhost/Epid?"
                    + "user=root&password=Password");

            // Statements allow to issue SQL queries to the database
            statement = connect.createStatement();
            // Result set get the result of the SQL query
            resultSet = statement.executeQuery("select last_insert_id()");
            int PatientID = 0;
            if (resultSet.next()) {
                PatientID = resultSet.getInt(1);
            }
            close();
           
        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }

    }

    private void writeMetaData(ResultSet resultSet) throws SQLException {
        // 	Now get some metadata from the database
        // Result set get the result of the SQL query

        System.out.println("The columns in the table are: ");

        System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
        for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
            System.out.println("Column " + i + " " + resultSet.getMetaData().getColumnName(i));
        }
    }

    private void writeResultSet(ResultSet resultSet) throws SQLException {
        // ResultSet is initially before the first data set
        while (resultSet.next()) {
            // It is possible to get the columns via name
            // also possible to get the columns via the column number
            // which starts at 1
            // e.g. resultSet.getSTring(2);
            String user = resultSet.getString("name");
            String website = resultSet.getString("sex");
            String summery = resultSet.getString("Ethnicity");
            Date date = resultSet.getDate("Occupation");
            String comment = resultSet.getString("District");
            System.out.println("User: " + user);
            System.out.println("Website: " + website);
            System.out.println("Summery: " + summery);
            System.out.println("Date: " + date);
            System.out.println("Comment: " + comment);
        }
    }

    // You need to close the resultSet
    private void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            //if (connect != null) {
              //  connect.close();
          //  }
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}
